Skip to main content

Notes on the Databases

All tables have been brought into a SQLITE Database. The Sqlite database has been brought into the sharepoint. It will be updated as more progress is done.

CREATE TABLE "tables" (
"DatabaseName" TEXT,
  "TableName" TEXT,
  "ColumnName" TEXT,
  "DataType" TEXT
  "Description" TEXT
)

ORSANCO_Bacteria_Contact_Rec.mdb​

This Database contains

  1. Tables
  2. Queries

The Queries have been pulled out of this database using VBA Code.

ORSANCO_Bimonthly.accdb​

This Database contains

  1. Tables
  2. Queries
  3. Forms
  4. Reports

The Queries have been pulled out of this database using VBA Code. The form has been documented.

ORSANCO Bimonthly Forms​

There is only 1 form in this database Detect-RDL Charts ORSANCO_Fish_Population ORSANCO_Fish_Population_Data

The table this references is QryDataRequests

ORSANCO Bimonthly Reports​

This report does not run. ORSANCO_Bimonthly_Report_QryQualityMonitorFormat ORSANCO_Bimonthly_Report_QryQualityMonitorFormat_error ORSANCO_Bimonthly_Report_QryQualityMonitorFormat ORSANCO_Bimonthly_Report_QryQualityMonitorFormat_error

It references the QryQualityMonitorFormat Query. This query does run but there is no Bimonthly SiteName in the query there are however [Bimonthly Sites].River and [Bimonthly Sites].Station_ID where the table is Bimonthly Sites.

ORSANCO_Bimonthly_Report_QryQualityMonitorFormat_query

In the Bimontly Sites Table there is a column called Bimonthly SiteName. So it is a confusing mix of query error and bad naming.

Biomonthly_Table

ORSANCO_Clean_Metals.mdb​

This Database contains

  1. Tables
  2. Queries
  3. Reports
  4. Macros
  5. Modules

The Queries have been pulled out of this database using VBA Code. There is 1 Query where the data could not be pulled out. It is QryBlanks20%Sample there is an error in this query. This query does show results and can be viewed in the designer but not SQL. Query Error for QryBlanks20%Sample in clean metals

Clean Metal Reports​

There are 10 reports in Clean Metals

~USGSComp​

This report Runs.
Source is ~USGSComp

ORSANCO_clean_metals_report_~USGSComp ORSANCO_clean_metals_report_~USGSComp_table

IntAsessPhysicals​

Source is QryPhysBottSurfAvg

ORSANCO_clean_metals_report_IntAsessPhysicals ORSANCO_clean_metals_report_IntAsessPhysicals_error

Integrated Assessment Data​

Source is QryIntAssessMetalsFlat

ORSANCO_clean_metals_report_Integrated_Assessment_Data ORSANCO_clean_metals_report_Integrated_Assessment_Data_error

Integrated Assessment Data for all surveys​

This report Runs.
Source is QryIntAssessmentXsecAvgs ORSANCO_clean_metals_report_Integrated_Assessment_Data_for_all_surveys

Integrated Assessment Data w Trib​

This report Runs.
Source is QryIntAssessmentXsecAvgs ORSANCO_clean_metals_report_Integrated_Assessment_Data_w_Trib

NonMetalsIntAssess​

This report Runs.
Source is QryIntAssessNutrients ORSANCO_clean_metals_report_NonMetalsIntAssess

NonMetalsIntAssess w Trib​

This report Runs.
Source is QryIntAssessNonMet-REPORT ORSANCO_clean_metals_report_NonMetalsIntAssess_w_Trib

Physicals Cross Sections​

This report Runs.
Source is QryPhysicals ORSANCO_clean_metals_report_Physical_Cross_Sections

QryFGDRpt​

This report Runs.
Source is QryFGDRpt ORSANCO_clean_metals_report_QryFGDRpt

QryFGDRptbySample​

This report Runs.
Source is QryFGDRpt ORSANCO_clean_metals_report_QryFGDRptbySample

Clean Metal Macros​

Output Event IDs​

SELECT [Metals Event].SiteName, [Metals Event].Date, [Metals Event].LocationID, [Metals Event].[Event ID], [Metals Event].IntAssesEvents, [Metals Event].EventComment, [Metals Event].[Event Type]
FROM [Metals Event]
WHERE ((([Metals Event].Date)>#9/1/2016#) AND (([Metals Event].[Event Type])="bimonthly"))
ORDER BY [Metals Event].Date DESC;

ORSANCO_clean_metals_macro_output_event_IDs

Output Sample IDs​

SELECT [Metals Sample].BatchID, [Metals Event].[Event Type], [Metals Event].IntAssesEvents, [Metals Sample].Date, [Metals Sample].[LIMS#], [Metals Sample].[DB ID], [Metals Sample].[Bottle#], [Metals Sample].SampleSite, [Metals Sample].[Event ID], [Metals Sample].[Sample Type], [Metals Sample].BatchID, [Metals Sample].Time, [Metals Sample].[Sample ID]
FROM [Metals Event] INNER JOIN [Metals Sample] ON [Metals Event].[Event ID] = [Metals Sample].[Event ID]
WHERE ((([Metals Sample].Date)>#9/1/2016#))
ORDER BY [Metals Sample].[DB ID] DESC;

ORSANCO_clean_metals_macro_output_sample_IDs

Clean Metal Modules​

Converted Macro - Output Sample IDs​

Option Compare Database

'------------------------------------------------------------
' Output_Sample_IDs_Output_Event_IDs
'
'------------------------------------------------------------
Function Output_Sample_IDs_Output_Event_IDs()
On Error GoTo Output_Sample_IDs_Output_Event_IDs_Err

  DoCmd.OpenQuery "QryDB IDs", acNormal, acEdit
  DoCmd.OutputTo acQuery, "QryDB IDs", "MicrosoftExcel(*.xls)", "T:/data/dissolved metals/formatted data/QryDB IDs.xls", True, ""
  DoCmd.Close acQuery, "QryDB IDs"

Output_Sample_IDs_Output_Event_IDs_Exit:
    Exit Function

Output_Sample_IDs_Output_Event_IDs_Err:
    MsgBox Error$
    Resume Output_Sample_IDs_Output_Event_IDs_Exit

End Function

Julian Date​

Option Compare Database

Function JulianDate()
Dim TheDate As Date, Jan1 As Date
Dim JulianDate As Integer
Jan1 = "1/1"
TheDate = Now
JulianDate = DateDiff(Jan1, TheDate) + 1
End Function

Sub test()
If MsgBox("Hello!", vbOKCancel) = vbOK Then
MsgBox "you clicked ok"
Else
MsgBox "you clicked cancel"
End If
End Sub

Function test2()
Dim a5 As Integer
a5 = 50
test2 = a5 * 2
End Function

ORSANCO_Fish.mdb​

This Database contains

  1. Tables -Make sure you are using terminal in admin mode when pulling out tables using python script.
  2. Queries
  3. Forms

The Queries have been pulled out of this database using VBA Code. The forms have been documented.

ORSANCO Fish Forms​

The 1st form is Event and is does not currently run. We can open it in viewer but we cannot see the data. I am looking into this. ORSANCO Fish Event ORSANCO Fish Event error

There is no table Fish_Data_Entry_Event and this is the table that the Form wants. If there is some table that contained this data before it is no longer here now. Filter for the population form

The other form is Population and it runs but there is not any data. I will be checking this form out as well. ORSANCO Fish Population ORSANCO Fish Population Data

Filter for the population form

(Fish_Data_Entry_Pop.[Notes(Pop)]<>"counted " Or Fish_Data_Entry_Pop.[Notes(Pop)] Is Null)

There is Nothing in this table. That is why there is no data in this Form.

ORSANCO_Macro.accdb​

This Database contains

  1. Tables
  2. Queries
  3. Macros

The Queries have been pulled out of this database using VBA Code.

ORSANCO Macro Macros​

ORSANCO_macro_macro_Candidate_count_metrics

SELECT [Macro Event Data].Macro_EventCode, [Step 1 - # Non-Insecta].LocCode, [Step 1 - # Non-Insecta].Pool, [Step 1 - # Non-Insecta].RMI, [Step 1 - # Non-Insecta].Year, Sum([Step 1 - # Non-Insecta].SumOfCount) AS SumOfSumOfCount, [Macro Event Data].Latitude
FROM [Macro Event Data] LEFT JOIN [Step 1 - # Non-Insecta] ON [Macro Event Data].Macro_EventCode = [Step 1 - # Non-Insecta].Macro_EventCode
GROUP BY [Macro Event Data].Macro_EventCode, [Step 1 - # Non-Insecta].LocCode, [Step 1 - # Non-Insecta].Pool, [Step 1 - # Non-Insecta].RMI, [Step 1 - # Non-Insecta].Year, [Macro Event Data].Latitude;

WSBacteriaDB.mdb​

This Database contains

  1. Tables
  2. Queries
  3. Reports
  4. Modules

The Queries have been pulled out of this database using VBA Code.

WSBacteriaDB Reports​

QryEColibyMile​

Source is QryRep2LMR

WSBacteriaDB_report_QryEColibyMile WSBacteriaDB_report_QryEColibyMile_error

WSBacteriaDB Modules​

The Module in this sheet is called Module2 and it is blank.

Last updated on by jacobgqc